#!/usr/bin/env python
#-*- coding: utf_8 -*-
try:
    import sqlite3
    print "CPython"
except:
    '''
    para utilizar a driver sqlJDBC criar ficheiro .barshrc com o texto 
    export CLASSPATH=/caminho para driver/sqlitejdbc-v056.jar:$CLASSPATH
    '''
    import sys
    sys.setdefaultencoding("utf-8") #Codificao por defeito utf-8    
    
    from com.ziclix.python.sql import zxJDBC
    print "Jython"

class JConverter:
    '''
    Class que lida com as ligacoes com a base de dados utilizando a driver sqliteJDBC-v052.jar
    '''
    def __init__(self,database,table):
        self.database = database
        print self.database
        self.__table = table
        JDBC_URL    = "jdbc:sqlite:%s"  % self.database
        JDBC_DRIVER = "org.sqlite.JDBC"
        self.dbConn = zxJDBC.connect(JDBC_URL, None, None, JDBC_DRIVER)
        self.cursor = self.dbConn.cursor()

    def createDB(self,fields):
        nor = self.normalize(fields)
        st = "create table if not exists \"" + self.__table + "\" (" + nor + ");"
        print st
        self.cursor.execute(st)
        self.dbConn.commit()



    def insertDB(self,data):
        reg = self.normalize(data)
        st = "insert into "+self.__table+" values("+reg+");"
        print st
        self.cursor.execute(st)
        self.dbConn.commit()

    def getValues(self):        
        self.cursor.execute("select * from "+self.__table+";")
        ls = []
        for row in self.cursor:
            ls.append(row)
        return ls

    def closeDB(self):
        self.dbConn.close()

    def normalize(self,word):
        res=''
        for x in word:           
            st = str(x)
            if("\'" in st):
                st = st.replace("\'","\'\'")
            if("\"" in st):
                st = st.replace("\"","\"\"")
            res = res + "'"+ st +"',"               
        return res[:-1]


class Converter:
    '''
    Esta class trata das ligacoes com a base de dados
    '''
    
    def __init__(self,database,table):
        self.database = database
        self.__table = table          
        pass
    '''
    Trata as strings para poderem ser enviados para a base de dados
    '''
    def normalize(self,word):
        res=''
        for x in word:           
            st = str(x)
            if("\'" in st):
                st = st.replace("\'","\'\'")
            if("\"" in st):
                st = st.replace("\"","\"\"")
            res = res + "'"+ st +"',"               
        return res[:-1]
        
    '''
    Cria a estrutura a base de dados e a tabela caso n exista
    '''
    def createDB(self,fields):
        self.fields = self.normalize(fields)
        connection = sqlite3.connect(self.database)
        cursor = connection.cursor()
        stmt = ('''CREATE TABLE if not exists "{0}"({1})''').format(self.__table,self.fields)        
        cursor.execute(stmt)        
        connection.commit()
        connection.close()
        
    '''
    Coloca um registo na base de dados
    '''
    def insertDB(self,data):
        connection = sqlite3.connect(self.database)
        cursor = connection.cursor()        
        data = self.normalize(data)
        stmt = ('''INSERT into "{0}" values({1})''').format(self.__table, data)
        cursor.execute(stmt)
        connection.commit()
        connection.close()
        print stmt
        
    '''
    Retorna todos os regitos da tabela
    '''
    def getValues(self):        
        stmt = '''select * from "{0}"'''.format(self.__table)
        connection = sqlite3.connect(self.database)
        cursor = connection.cursor()    
        cursor.execute(stmt)
        ls=[]
        for row in cursor:
            ls.append(row)
            pass            
        connection.close()
       
        return ls    
            
    

        
